package org.dromara.system.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.dromara.system.domain.vo.StoreOrderMonthVo;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * @author 15063
 * @title: TxTuanGouMapper
 * @projectName zhsq-daping-master
 * @description: TODO
 * @date 2025/9/1516:39
 */
@DS("tg-tx")
@Mapper
public interface TxTuanGouMapper {
    //select * from 表名 WHERE (时间字段名) =
    @Select("SELECT COUNT(id) FROM  eb_store_order WHERE paid = 1 AND TO_DAYS(create_time) = TO_DAYS(now())")
    Integer queryTodayOrderNum();

    @Select("SELECT COUNT(id) FROM  eb_store_order WHERE paid = 1")
    Integer queryTotalOrderNum();

    @Select("SELECT  IFNULL(sum(pay_price), 0) as pay_price FROM eb_store_order WHERE paid = 1")
    BigDecimal queryTotalOrderSumMoney();

    /**
     * 查询商品数量
     */
    @Select("SELECT COUNT(id) FROM  eb_store_product WHERE is_del = 0 AND is_show = 1")
    Integer queryGoodsNum();

    /**
     * 查询分类数量
     */
    @Select("SELECT COUNT(id) FROM  eb_category WHERE pid != 0 AND `status` = 1")
    Integer queryCountCategory();


    /**
     * 查询商品top10
     */
    @Select("SELECT id, store_name AS storeName, sales, image  FROM  eb_store_product WHERE is_del = 0 ORDER BY sales DESC LIMIT 10")
    Map<String,Object> queryTop10GoodsData();


    /**
     * 各个分类商品的销售占比，统计两个社区所有的商品分类情况及其占比情况
     */
    @Select("SELECT  c.id category_id, c.name category_name, SUM(oi.pay_num * oi.price) AS total_sales,  ROUND(  SUM(oi.pay_num * oi.price) /   (SELECT SUM(oi2.pay_num * oi2.price) FROM eb_store_order_info oi2  JOIN eb_store_order o2 ON oi2.order_id = o2.id  WHERE o2.status != 1) * 100, 2) AS sales_percentage FROM eb_category c JOIN eb_store_product p ON FIND_IN_SET(c.id, p.cate_id) > 0 JOIN eb_store_order_info oi ON p.id = oi.product_id JOIN eb_store_order o ON oi.order_id = o.id WHERE o.status != 1 GROUP BY c.id, c.name ORDER BY total_sales DESC")
    Map<String,Object> queryCategoryGoods();


    /**
     * 获取月度订单趋势
     */
    @Select("WITH month_series AS ( SELECT TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -n), 'YYYY-MM') AS order_month FROM (SELECT LEVEL - 1 AS n FROM DUAL CONNECT BY LEVEL <= 12))" +
        "SELECT ms.order_month AS orderMonth, COALESCE(COUNT(eso.ID), 0) AS orderCount, COALESCE(COUNT(DISTINCT eso.UID), 0) AS uniqueCustomers, COALESCE(SUM(eso.TOTAL_PRICE), 0) AS totalSales" +
        "FROM month_series ms LEFT JOIN eb_store_order eso ON TO_CHAR(eso.PAY_TIME, 'YYYY-MM') = ms.order_month AND eso.STATUS = 3 AND eso.PAY_TIME >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -12) GROUP BY ms.order_month ORDER BY ms.order_month")
    List<StoreOrderMonthVo> queryMonthOrderGoods();


}
